package exttable.testfiller;

import dbutils.DbUtils;
import dbutils.StringQueries;
import exttable.SGTTestParametrs;
import exttable.testselector.TestUtils;
import exttable.testselector.VolunteerSelectorController;
import exttable.testselector.VolunteerSelectorMVC;
import exttable.view.ViewAnalysis;
import options.AppSettings;
import utils.Localisation;
import utils.UserFileFactory;

import javax.swing.*;
import java.awt.*;
import java.awt.event.ActionEvent;
import java.awt.event.ActionListener;
import java.io.File;
import java.sql.*;
import java.util.*;
import java.util.List;

/**
 * Created by IntelliJ IDEA.
 * User: Admin
 * Date: Jan 29, 2010
 * Time: 11:46:16 AM
 * To change this template use File | Settings | File Templates.
 */
class ResultPanel {

    private JPanel resultPanle;
    private String tableName;
    private final Vector<String> columnNames = new Vector<String>(4);
    private Vector<Vector> rowData = new Vector<Vector>();
    private JTable table;
    private int probId;
    private int groupSchId;
    private int visit;
    private String userName;
    private ViewAnalysis viewAnalysis;
    private JFrame f;
    public static final String NOT_IN_VERSION = "-2";
    private String mode;
    private Map<String, Object[]> modifiedValues = new HashMap<String, Object[]>();


    ResultPanel(Vector<Vector> mapData, String tableName, int probId, int groupSchId, int visit, ViewAnalysis viewAnalysis, String mode) {
        this.tableName = tableName;
        this.probId = probId;
        this.groupSchId = groupSchId;
        this.visit = visit;
        this.userName = SGTTestParametrs.getInstance().getUserName();
        this.viewAnalysis = viewAnalysis;
        columnNames.add(Localisation.getMessage("form.analysparam"));
        columnNames.add(Localisation.getMessage("form.analysvalue"));
        columnNames.add(Localisation.getMessage("table.column.insertcolumn"));
        columnNames.add(Localisation.getMessage("table.column.insertvalue"));
        rowData.addAll(mapData);
        this.mode = mode;
    }


    public JFrame getResultFrame() {
        f = new JFrame(Localisation.getMessage("title.resultcheck"));
        JButton saveButton = new JButton(Localisation.getMessage("form.saveresult"));
        f.setLayout(new BorderLayout());

        resultPanle = new JPanel();
        resultPanle.setLayout(new BorderLayout());

        rowData.addAll(getOutOfVersionColumns());
        table = new JTable(rowData, columnNames);
        JScrollPane js = new JScrollPane(table);
        resultPanle.add(js);

        saveButton.addActionListener(new ActionListener() {
            @Override
            public void actionPerformed(ActionEvent e) {
                try {
                    gatherColumnsValues(table, tableName, getTestId(probId, visit, groupSchId), userName, viewAnalysis);
                    f.setDefaultCloseOperation(JFrame.HIDE_ON_CLOSE);
                    f.dispose();
                    InputFormDisplay.frame.dispose();
                    //TODO fix
                    VolunteerSelectorMVC.getInstance().getController().updateTimeTable(VolunteerSelectorMVC.getInstance().getModel().getTestParametrs());
                } catch (Exception exx) {
                    exx.printStackTrace();
                }
            }
        });
        resultPanle.setVisible(true);
        f.add(resultPanle, BorderLayout.CENTER);
        f.add(saveButton, BorderLayout.SOUTH);
        f.setVisible(true);
        f.pack();
        return f;
    }

    private Vector getOutOfVersionColumns() {
        Vector<Vector> toReturn = new Vector<Vector>();
        try {
            Connection c = DbUtils.getDBConnectionGeneral();
            String query = String.format(StringQueries.FIELDS_OUT_OF_VERSION, getColumnsList());
            PreparedStatement ps = c.prepareStatement(query);
            ps.setString(1, tableName);
            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                Vector<Object> v = new Vector<Object>();
                v.add(Localisation.getMessage("table.row.notinversion"));
                v.add(Localisation.getMessage("table.row.notinversion"));
                v.add(rs.getString("analys_param"));
                v.add(NOT_IN_VERSION);
                toReturn.add(v);
            }
            return toReturn;
        } catch (Exception e) {
            e.printStackTrace();
            throw new IllegalArgumentException("err");
        }

    }

    private String getColumnsList() {
        StringBuilder toReturn = new StringBuilder();
        int i = 0;
        for (Vector res : rowData) {
            i++;
            toReturn.append("'");
            toReturn.append(res.get(2));
            toReturn.append("'");
            if (i < rowData.size()) {
                toReturn.append(", ");
            }
        }
        return toReturn.toString();
    }


    private Integer getTestId(int probId, int visitId, int groupSch) throws Exception {
//        System.out.println("probID: " + probId + " visit: " + visitId + " gropSH: " + groupSch);
        Integer toReturn = null;
        try {
            Connection connOriginal = DbUtils.getDBConnectionGeneral();
            PreparedStatement psOriginal = connOriginal.prepareStatement(StringQueries.TEST_ID);
            psOriginal.setInt(1, probId);
            psOriginal.setInt(2, visitId);
            psOriginal.setInt(3, groupSch);
            System.out.println(psOriginal.toString());

            ResultSet rsOriginal = psOriginal.executeQuery();
            while (rsOriginal.next()) {
                toReturn = rsOriginal.getInt(1);
            }
            connOriginal.close();
            if (toReturn == null & DbUtils.mapDataBases.get(DbUtils.SECONDARY_DATABASE_NAME)!=null) {
//                String queryCopy = psOriginal.toString();
//                queryCopy = queryCopy.replace("fs1.", "fs1_copy.");
                Connection connCopy = DbUtils.getDBConnectionSecondary();
//                PreparedStatement psCopy=connCopy.prepareStatement(queryCopy);
                PreparedStatement psCopy = connCopy.prepareStatement(StringQueries.TEST_ID);
                psCopy.setInt(1, probId);
                psCopy.setInt(2, visitId);
                psCopy.setInt(3, groupSch);
                ResultSet rsCopy = psCopy.executeQuery();
                while (rsCopy.next()) {
                    toReturn = rsCopy.getInt(1);
                }
                connCopy.close();
                if (toReturn == null) {
//                    toReturn = createTestId(probId, visitId, groupSch, userName);
                    toReturn=getMaxTestIdFromVisitDetails();
                    insertTestID(toReturn, probId, visitId, groupSch, userName);
                }else {
                    insertTestID(toReturn, probId, visitId, groupSch, userName);
                }

            }else if(toReturn==null & DbUtils.mapDataBases.get(DbUtils.SECONDARY_DATABASE_NAME)==null) {
              toReturn = createTestId(probId, visitId, groupSch, userName);
            }
            return toReturn;
        } catch (Exception e) {
            throw new Exception("no connect");
        }
    }

    private void insertTestID(int testId, int probId, int visitId, int groupSch, String userName){
        try{
            Connection conn=DbUtils.getDBConnectionGeneral();
            PreparedStatement ps=conn.prepareStatement(StringQueries.INSERT_INTO_VISIT_DETAILS);
            ps.setInt(1, testId);
            ps.setInt(2, probId);
            ps.setInt(3, visitId);
            ps.setString(4, userName);
            ps.setString(5, new Timestamp(Calendar.getInstance().getTime().getTime()).toString());
            ps.setInt(6, groupSch);
            ps.executeUpdate();
            conn.close();
        }catch (Exception e){
            e.printStackTrace();
        }
    }

    private Integer getMaxTestIdFromVisitDetails(){
        Integer maxTestId=null;
        Integer maxTestIdFromOriginal=null;
        Integer maxTestIdFromCopy=null;
        try{
            Connection connOriginal=DbUtils.getDBConnectionGeneral();
            PreparedStatement psOriginal=connOriginal.prepareStatement("select max(test_id) from visit_details");
            ResultSet rsOriginal=psOriginal.executeQuery();

            Connection connCopy=DbUtils.getDBConnectionSecondary();
            PreparedStatement psCopy=connCopy.prepareStatement("select max(test_id) from visit_details");
            ResultSet rsCopy=psCopy.executeQuery();
            while(rsOriginal.next()){
               maxTestIdFromOriginal=rsOriginal.getInt(1);
            }
            while (rsCopy.next()){
                maxTestIdFromCopy=rsCopy.getInt(1);
            }
            connOriginal.close();
            psOriginal.close();
            rsOriginal.close();

            connCopy.close();
            psCopy.close();
            rsCopy.close();
            if(maxTestIdFromOriginal > maxTestIdFromCopy){
              maxTestId=maxTestIdFromOriginal+1;
            }else{
               maxTestId=maxTestIdFromCopy+1;
            }
        }catch (Exception e){
            e.printStackTrace();
        }
        return maxTestId;
    }
    private Integer createTestId(int probId, int visitId, int groupSch, String userName) throws Exception {
        try {
            Connection c = DbUtils.getDBConnectionGeneral();
            PreparedStatement ps = c.prepareStatement(StringQueries.INSERT_TEST);
            ps.setInt(1, probId);
            ps.setInt(2, visitId);
            ps.setString(3, userName);
            ps.setString(4, new Timestamp(Calendar.getInstance().getTime().getTime()).toString());
            ps.setInt(5, groupSch);
            ps.executeUpdate();
            c.close();
            return getTestId(probId, visitId, groupSch);
        } catch (Exception e) {
            e.printStackTrace();
            throw new Exception("DB error");
        }

    }

    private void gatherColumnsValues(JTable table, String tableName, Integer testId, String userName, ViewAnalysis version) throws Exception {
        List<String> columnNames = new LinkedList<String>();
        List<String> columnValues = new LinkedList<String>();

        for (int i = 0; i < table.getModel().getRowCount(); i++) {
            columnNames.add(table.getModel().getValueAt(i, 2).toString());
            columnValues.add(table.getModel().getValueAt(i, 3).toString().replace(',', '.'));
        }

        generateInsertStatement(columnNames, columnValues, testId, userName, SGTTestParametrs.getInstance().isExceedLimits(), tableName, version);
    }

    private void generateInsertStatement(List<String> columnNames, List<String> columnValues, Integer testId, String user
            , boolean exceedMax, String tableName, ViewAnalysis version) throws Exception {
        String filedNames = "";
        String values = "";

        //test id
        columnNames.add(TestUtils.universalColumnsArray.get(0));
        columnValues.add(String.valueOf(testId));
        //version
        columnNames.add(TestUtils.universalColumnsArray.get(6));
        columnValues.add(version.getAnalysisVersionKey());

        if (mode.equals("NEW")) {
            //user
            columnNames.add(TestUtils.universalColumnsArray.get(1));
            columnValues.add(user);
            //insert date time
            columnNames.add(TestUtils.universalColumnsArray.get(2));
            columnValues.add(new Timestamp(Calendar.getInstance().getTime().getTime()).toString());

            for (String res : columnNames) {
                filedNames = filedNames + res + ", ";
            }

            for (String res : columnValues) {
                values = values + "'" + res + "'" + ", ";
            }

            filedNames = filedNames + TestUtils.universalColumnsArray.get(5) + ", ";
            values = values + String.valueOf(exceedMax) + ", ";

            filedNames = filedNames.substring(0, filedNames.length() - 2);
            values = values.substring(0, values.length() - 2);

            String toInsert = "insert into " + tableName + "( " + filedNames + " )" + " values (" + values + " )";
//            System.out.println("insert statement: " + toInsert);
//            addNewRecord(toInsert);
            if(InputFormDisplay.dbFile==null){
                addNewRecord(toInsert);
            }else {
                addNewRecordWithTransaction(generateFileQuery(),toInsert);
            }
        }
        if (mode.equals("EDIT")) {
            //modified_user
            columnNames.add(TestUtils.universalColumnsArray.get(3));
            columnValues.add(user);
            //modified_date
            columnNames.add(TestUtils.universalColumnsArray.get(4));
            columnValues.add(new Timestamp(Calendar.getInstance().getTime().getTime()).toString());
            StringBuilder query = new StringBuilder();
            query.append("UPDATE ").append(tableName).append(" ").append(" set ");
            for (int i = 0, columnNamesSize = columnNames.size(); i < columnNamesSize; i++) {
                String column = columnNames.get(i);
                String value = columnValues.get(i);
                query.append(column).append(" = ").append("'").append(value).append("'").append(", ");
            }
            String querySts = query.substring(0, query.length() - 2);
            querySts = querySts + " where test_id = " + testId;
//            System.out.println("Q: " + querySts);
            fillModifiedValues(testId, tableName, columnNames, columnValues);
//            if (addNewRecord(querySts)) {
//                saveModifiedValues(testId, tableName, user);
//            }
            if(InputFormDisplay.dbFile==null){
                addNewRecord(querySts);
            }else {
                addNewRecordWithTransaction(generateFileQuery(),querySts);
            }
        }

    }

    private void saveModifiedValues(Integer testId, String tableName, String user) throws Exception {
        Connection c = DbUtils.getDBConnectionGeneral();
        PreparedStatement ps = c.prepareStatement("INSERT INTO test_history " +
                " (table_name, column_name, old_value, new_value, modified_time, modified_user, test_id) " +
                "    VALUES(?, ?, ?, ?, ?, ?, ?)");
        for (Map.Entry<String, Object[]> res : modifiedValues.entrySet()) {
            ps.setString(1, tableName);
            ps.setString(2, res.getKey());
            ps.setObject(3, res.getValue()[0]);
            ps.setObject(4, res.getValue()[1]);
            ps.setString(5, new Timestamp(Calendar.getInstance().getTime().getTime()).toString());
            ps.setString(6, user);
            ps.setInt(7, testId);
            ps.executeUpdate();
        }

    }

    private boolean addNewRecord(String query) throws Exception {
        boolean toReturn;
        try {
            Connection c = DbUtils.getDBConnectionGeneral();
            PreparedStatement ps = c.prepareStatement(query);
            int a = ps.executeUpdate();
            if (a == 1) {
                JOptionPane.showMessageDialog(null, Localisation.getMessage("info.dataadded"), Localisation.getMessage("info.title"), JOptionPane.INFORMATION_MESSAGE);
                toReturn = true;
            } else {
                JOptionPane.showMessageDialog(null, Localisation.getMessage("error.dataerror"), Localisation.getMessage("error.title"), JOptionPane.WARNING_MESSAGE);
                toReturn = false;
            }
            c.close();
            return toReturn;
        } catch (Exception e) {
            e.printStackTrace();
        }
        return false;
    }

    private boolean addNewRecordWithTransaction(String fileQuery, String query) throws Exception {
        boolean toReturn;
        Connection conn = null;
        try {
            conn = DbUtils.getDBConnectionGeneral();
            Statement statement;

            conn.setAutoCommit(false);
            statement = conn.createStatement();

            statement.executeUpdate(fileQuery);
            statement.executeUpdate(query);
            statement.close();
            conn.commit ();
            conn.setAutoCommit(true);

            JOptionPane.showMessageDialog(null, Localisation.getMessage("info.dataadded"), Localisation.getMessage("info.title"), JOptionPane.INFORMATION_MESSAGE);
            toReturn = true;
            String pathToDir= VolunteerSelectorController.viewProbationer.getProbationCode()+"\\"+InputFormDisplay.getViewAnalysis().getAnalysisName()+"\\";
            File checkPath=new File(AppSettings.get("generalSettings","saveFileTo","").toString()+"\\"+pathToDir);
            if(!checkPath.exists()){
                checkPath.mkdirs();
            }
            File file=new File(AppSettings.get("generalSettings","saveFileTo","").toString()+"\\"+pathToDir+InputFormDisplay.dbFile.getFileName()+"."+InputFormDisplay.dbFile.getFileEhtension());
            UserFileFactory.copyFile(InputFormDisplay.selectedFile, file);
            return toReturn;
        }catch (SQLException sqlE){
            System.err.println ("Transaction failed, rolling back.");
            JOptionPane.showMessageDialog(null, Localisation.getMessage("error.dataerror"), Localisation.getMessage("error.title"), JOptionPane.WARNING_MESSAGE);
            toReturn = false;
            // пустой обработчик исключений, если откат не удался
            try{
                conn.rollback ();
                conn.setAutoCommit (true);
            }
            catch (Exception e2) { }

        }
        catch (Exception e) {
            e.printStackTrace();
        }
        finally {
            conn.close();
        }
        return false;
    }

    private void fillModifiedValues(int testId, String tableName, List<String> columnNames, List<String> columnValues) throws Exception {
        Map<String, String> newValues = new HashMap<String, String>();
        for (int i = 0, columnNamesSize = columnNames.size(); i < columnNamesSize; i++) {
            newValues.put(columnNames.get(i), columnValues.get(i));
        }

        Connection c = DbUtils.getDBConnectionGeneral();
        PreparedStatement ps = c.prepareStatement("SELECT * FROM " + tableName + " where test_id = ?");
        ps.setInt(1, testId);
        ResultSet rs = ps.executeQuery();
        ResultSetMetaData rsmd = rs.getMetaData();
        rs.next();
        for (int i = 1; i <= rsmd.getColumnCount(); i++) {
            modifiedValues.put(rsmd.getColumnName(i), new Object[]{rs.getString(i), null});
        }
        c.close();
        for (String res : TestUtils.universalColumnsArray) {
            modifiedValues.remove(res);
        }

        for (Map.Entry<String, Object[]> res : modifiedValues.entrySet()) {
            if (!newValues.get(res.getKey()).equals(res.getValue()[0])) {
                res.setValue(new Object[]{res.getValue()[0], newValues.get(res.getKey())});
            }
        }

        for (Iterator<Map.Entry<String, Object[]>> iterator = modifiedValues.entrySet().iterator(); iterator.hasNext(); ) {
            Map.Entry<String, Object[]> res = iterator.next();
            if (res.getValue()[1] == null) {
                iterator.remove();
            }
        }


    }

    private String generateFileQuery(){
        String fileQuery=null;
        Connection connGeneral=null;
        PreparedStatement psGeneral=null;
        ResultSet rs=null;
        try {
            connGeneral=DbUtils.getDBConnectionGeneral();
            psGeneral=connGeneral.prepareStatement("SELECT file_id FROM files WHERE file_name=?");
            psGeneral.setString(1,InputFormDisplay.dbFile.getFileName());
            rs=psGeneral.executeQuery();
            if(rs.next()){
                fileQuery="UPDATE files SET "+
                        " file_ehtension='"+InputFormDisplay.dbFile.getFileEhtension()+"',"+
                        " source_file_name='"+InputFormDisplay.dbFile.getSourceFileName()+"',"+
                        " sumCRC32='"+InputFormDisplay.dbFile.getSumCRC32()+"'"+
                        " WHERE file_id="+InputFormDisplay.dbFile.getFileId();
            }else {
                String fileValues=InputFormDisplay.dbFile.getFileId()+",'"+
                        InputFormDisplay.dbFile.getFileName()+"','"+
                        InputFormDisplay.dbFile.getFileEhtension()+"','"+
                        InputFormDisplay.dbFile.getSourceFileName()+"','"+
                        InputFormDisplay.dbFile.getTableName()+"','"+
                        InputFormDisplay.dbFile.getSumCRC32()+"'";
                fileQuery="INSERT INTO files values ("+fileValues+");";
            }
            System.out.println("psGeneral : "+psGeneral.toString());
            connGeneral.close();
            psGeneral.close();
            rs.close();

            System.out.println("fileQuery : "+fileQuery);

        } catch (ClassNotFoundException e) {
            e.printStackTrace();  //To change body of catch statement use File | Settings | File Templates.
        } catch (SQLException e) {
            e.printStackTrace();  //To change body of catch statement use File | Settings | File Templates.
        }

        return fileQuery;
    }

}


